package com.apes.enquiry.product.repository;

import com.apes.enquiry.brand.model.SpBrand;
import com.apes.enquiry.product.model.SpProduct;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.party.model.PartyRole;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Set;

/**
 * 类描述：
 *
 * @ClassName SpProductRepository
 * @Description 描述
 * @Author wushilong
 * @Date 2022/10/14 14:04
 * @Version 1.0
 */
public interface SpProductRepository extends CustomRepository<SpProduct,String> {
     @Query("SELECT T FROM  SpProduct T  where T.OE=:pn")
     SpProduct findBypn(@Param("pn") String pn);

     @Query("select a from SpProduct a where a.OE = :pn and a.spBrand = :spBrand and a.supplier = :partyRole AND NAME = :name")
     SpProduct findBypnAndSpBrand(@Param("pn") String pn,SpBrand spBrand, PartyRole partyRole, String name);

     @Query(value = "select a from SpProduct a where a.OE = :pn and a.spBrand = :spBrand and a.supplier = :supplier and a.name=:name")
     SpProduct findByPNAndSpBrand(String pn, SpBrand spBrand , PartyRole supplier,String name);

     @Query(value = "SELECT a.pn||(SELECT b.ppmc FROM sp_mas_ppxx b WHERE a.brand_id = b.id)||a.supplier_id as pn FROM sp_mas_product a", nativeQuery = true)
     Set<String> findPNAndBrandAll();

     //@Query(value = "SELECT A FROM SpProduct A WHERE A.id=:id AND A.notTaxUnitPrice <> 0 AND A.merchantStockQty <> 0 ")
     @Query(value = "SELECT A FROM SpProduct A WHERE A.id=:id")
     SpProduct findByIdAndPriceAndQuantity(@Param("id") String id);

     //@Query(value = "SELECT A FROM SpProduct A WHERE A.PN=:PN AND A.category.id=:id AND A.notTaxUnitPrice <> 0 AND A.merchantStockQty <> 0 ")
     @Query(value = "SELECT A FROM SpProduct A WHERE A.OE=:PN AND A.category.id=:id  ")
     Set<SpProduct> findByPNByCategory(@Param("PN") String PN,@Param("id") String id);

     //@Query(value = "SELECT * FROM sp_mas_product WHERE PN=:PN AND automobile_id=:id AND not_tax_unit_price!=0 AND merchant_stock_qty!=0 ", nativeQuery = true)
     @Query(value = "SELECT * FROM sp_mas_product WHERE PN=:PN AND automobile_id=:id ", nativeQuery = true)
     Set<SpProduct> findByPNByAutomobile(@Param("PN") String PN,@Param("id") String id);

     //@Query(value = "SELECT A FROM SpProduct A WHERE A.PN=:PN AND A.spBrand=:brand AND A.notTaxUnitPrice <> 0 AND A.merchantStockQty <> 0 ")
     @Query(value = "SELECT A FROM SpProduct A WHERE A.OE=:PN AND A.spBrand=:brand ")
     Set<SpProduct> findByPNBySpBrand(@Param("PN") String PN,@Param("brand") SpBrand  brand);

     @Query(value = "SELECT a.* FROM sp_mas_product a WHERE a.id=:id AND a.product_state='offer'", nativeQuery = true)
     SpProduct findBySupplierAndState(@Param("id") String id);

     @Query(value = "select a from SpProduct a where a.OE like :param and a.supplier.id = :supplierId AND a.productState != 'notOffer' ")
     Page<SpProduct> findAllLikeIdAndName(String param, String supplierId, Pageable var2);

     @Query(value = "select a from SpProduct a where a.OE = :OE and a.spBrand.id = :brand and a.name = :name and a.supplier.id = :supplier")
     List<SpProduct> findOneByBrandAndSupplier(String OE, String brand, String supplier, String name);

     @Query(value = "SELECT COUNT(*) FROM sp_mas_product  WHERE mate_state ='fail' AND standard_type IS NOT NULL", nativeQuery = true)
     Integer findCountByMateState();

     @Query(value = "SELECT * FROM sp_mas_product  WHERE mate_state ='fail' AND standard_type IS NOT NULL ", nativeQuery = true)
     List<SpProduct> findByMasteState();
     @Query(value = "select a from SpProduct a where a.name like :name and a.productState = 'offer' and a.notTaxUnitPrice > 0 and a.merchantStockQty > 0 and a.standardType ='Accumulator' and a.temporary=false")
     List<SpProduct> findbyNameAndAccumulator(String name);

     @Query(value = "select a from SpProduct a where a.name like :name and a.productState = 'offer' and a.notTaxUnitPrice > 0 and a.merchantStockQty > 0 and a.standardType ='Tyre' and a.temporary=false")
     List<SpProduct> findbyNameAndTyre(String name);

     @Query(value = "select a from SpProduct a where a.name like :name and a.productState = 'offer' and a.notTaxUnitPrice > 0 and a.merchantStockQty > 0 and a.standardType ='Oily' and a.temporary=false")
     List<SpProduct> findbyNameAndOily(String name);

     @Query(value = "select a from SpProduct a where a.name like :name and a.productState = 'offer' and a.notTaxUnitPrice > 0 and a.merchantStockQty > 0 and a.standardType='Tyre' and a.temporary=false")
     List<SpProduct> findbyName(String name);

     @Query(value = "SELECT * FROM sp_mas_product  WHERE pn=:OE AND temporary =1", nativeQuery = true)
     List<SpProduct> findByOEAndTemporary(@Param("OE")String OE);

     @Query(value = "select a from SpProduct a where a.name = :name and a.supplier.id = :supplierId")
     SpProduct findByNameAndSupplier(String name, String supplierId);
}
